-- exec SP_YIELD_COST_REPORT 1348, 2005, 0

ALTER PROC SP_YIELD_COST_REPORT ( @PROCESSSTEP_ID INT, @YEAR INT, @QUARTER INT ) AS
BEGIN

  	DECLARE @FG_TABLE TABLE( FG_processStep_Id INT )
	DECLARE @Process_Table TABLE( ProcessStep_ID INT, PS_Type_Name VARCHAR(50) )
	DECLARE @Result_Table TABLE( 	FG_Part_Number VARCHAr(50),
					ProcessStep_Id INT,
					Year INT,
					Quarter INT,
					FAB_Standard_Cost NUMERIC( 15, 4 ),
					FAB_Standard_yield NUMERIC( 15, 4 ),
					FAB_Yielded_Yield NUMERIC( 15, 4 ),
					WS_Standard_Cost NUMERIC( 15, 4 ),
					WS_Standard_yield NUMERIC( 15, 4 ),
					WS_Yielded_Yield NUMERIC( 15, 4 ),
					SORT_Standard_Cost NUMERIC( 15, 4 ),
					SORT_Standard_yield NUMERIC( 15, 4 ),
					SORT_Yielded_Yield NUMERIC( 15, 4 ),
					DB_Standard_Cost NUMERIC( 15, 4 ),
					DB_Standard_yield NUMERIC( 15, 4 ),
					DB_Yielded_Yield NUMERIC( 15, 4 ),
					ASSM_Standard_Cost NUMERIC( 15, 4 ),
					ASSM_Standard_yield NUMERIC( 15, 4 ),
					ASSM_Yielded_Yield NUMERIC( 15, 4 ),
					TEST_Standard_Cost NUMERIC( 15, 4 ),
					TEST_Standard_yield NUMERIC( 15, 4 ),
					TEST_Yielded_Yield NUMERIC( 15, 4 ),
					CS_Standard_Cost NUMERIC( 15, 4 ),
					CS_Standard_yield NUMERIC( 15, 4 ),
					CS_Yielded_Yield NUMERIC( 15, 4 ),
					MP_Standard_Cost NUMERIC( 15, 4 ),
					MP_Standard_yield NUMERIC( 15, 4 ),
					MP_Yielded_Yield NUMERIC( 15, 4 ),
					FG_Standard_Cost NUMERIC( 15, 4 ),
					FG_Standard_yield NUMERIC( 15, 4 ),
					FG_Yielded_Yield NUMERIC( 15, 4 )	)
	DECLARE @FG_CURSOR CURSOR
	DECLARE @Process_Cursor CURSOR
	DECLARE @FG_ProcessStep_Id INT
	DECLARE @Process_ProcessStep_Id INT
	DECLARE @Process_Type VARCHAR(50)
	DECLARE @Avg_Standard_Cost NUMERIC(15, 4)
	DECLARE @Avg_Standard_Yield NUMERIC(15, 4)
	DECLARE @Avg_Actual_Yield NUMERIC(15, 4)
	DECLARE @Quarter_Start_Date DATETIME
	DECLARE @Quarter_End_Date DATETIME

	-- IF the Quarter is not a Valid Quarter get the Current Quarter
	IF @QUARTER IS NULL OR @QUARTER < 1 OR @QUARTER > 4
	BEGIN
		SET @QUARTER = DATEPART ( QQ, CURRENT_TIMESTAMP ) 
	END

	IF ( @QUARTER = 1 ) 
	BEGIN
		SELECT @Quarter_Start_Date = CQC_Start_Date, @Quarter_End_Date = CQC_End_Date FROM Company_Quarterly_calendar WHERE YEAR( CQC_Start_Date ) = @YEAR AND CQC_Quarter = 'Quarter 1'
	
		IF @Quarter_Start_Date IS NULL
		BEGIN
			SET @Quarter_Start_Date = '01/01/' + CONVERT( VARCHAR, @YEAR )
			SELECT @Quarter_End_Date = DATEADD( dd, -1, DATEADD( mm, 3, @Quarter_Start_Date ) )
		END

	END
	IF ( @QUARTER = 2 )
	BEGIN
		SELECT @Quarter_Start_Date = CQC_Start_Date FROM Company_Quarterly_calendar WHERE YEAR( CQC_Start_Date ) = @YEAR AND CQC_Quarter = 'Quarter 1'
		SELECT @Quarter_Start_Date = DATEADD( mm, 3, @Quarter_Start_Date )
		SELECT @Quarter_End_Date = DATEADD( dd, -1, DATEADD( mm, 3, @Quarter_Start_Date ) )

		IF @Quarter_Start_Date IS NULL
			SET @Quarter_Start_Date = '04/01/' + CONVERT( VARCHAR, @YEAR )
		
		SELECT @Quarter_End_Date = DATEADD( dd, -1, DATEADD( mm, 3, @Quarter_Start_Date ) )
	END 
	IF ( @QUARTER = 3 ) 
	BEGIN
		SELECT @Quarter_Start_Date = CQC_Start_Date FROM Company_Quarterly_calendar WHERE YEAR( CQC_Start_Date ) = @YEAR AND CQC_Quarter = 'Quarter 1'
		SELECT @Quarter_Start_Date = DATEADD( mm, 6, @Quarter_Start_Date )

		IF @Quarter_Start_Date IS NULL
			SET @Quarter_Start_Date = '07/01/' + CONVERT( VARCHAR, @YEAR )

		SELECT @Quarter_End_Date = DATEADD( dd, -1, DATEADD( mm, 3, @Quarter_Start_Date ) )
	END
	IF ( @QUARTER = 4 ) 
	BEGIN
		SELECT @Quarter_Start_Date = CQC_Start_Date FROM Company_Quarterly_calendar WHERE YEAR( CQC_Start_Date ) = @YEAR AND CQC_Quarter = 'Quarter 1'
		SELECT @Quarter_Start_Date = DATEADD( mm, 9, @Quarter_Start_Date )
		
		IF @Quarter_Start_Date IS NULL
			SET @Quarter_Start_Date = '10/01/' + CONVERT( VARCHAR, @YEAR )

		SELECT @Quarter_End_Date = DATEADD( dd, -1, DATEADD( mm, 3, @Quarter_Start_Date ) )
	END

	

	-- IF All is selected we need to show all the Finished Goods
	IF @PROCESSSTEP_ID = 0 OR @PROCESSSTEP_ID = -1
	BEGIN
		INSERT INTO @FG_TABLE SELECT Product.ProcessStep_ID from Product, Process_Step where Product.ProcessStep_ID = Process_Step.ProcessStep_ID AND P_type IS NULL AND Process_Step.PS_Type_Name= 'Finished Goods' 
	END
	ELSE
	BEGIN
		INSERT INTO @FG_TABLE VALUES ( @PROCESSSTEP_ID )
	END
	
	-- Fetch all the FG from the temp table

	SET @FG_CURSOR = CURSOR FOR SELECT FG_processStep_Id FROM @FG_TABLE
	OPEN @FG_CURSOR
	FETCH NEXT FROM @FG_CURSOR INTO @FG_ProcessStep_Id

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM @Process_Table
		INSERT INTO @Process_Table SELECT ProcessStep_ID, Process_Type_Name FROM fn_get_process_flow_by_processstep_id( @FG_ProcessStep_Id )
		INSERT INTO @Result_Table ( ProcessStep_Id, Year, Quarter ) VALUES ( @FG_ProcessStep_Id, @YEAR, @QUARTER )

		SET @Process_Cursor = CURSOR FOR SELECT ProcessStep_ID, PS_Type_Name FROM @Process_Table
		OPEN @Process_Cursor
		FETCH NEXT FROM @Process_Cursor INTO @Process_ProcessStep_Id, @Process_Type
		WHILE  @@FETCH_STATUS = 0
		BEGIN
			-- Fetch the Standard Cost and Standard Yield
			IF ( @QUARTER = 1 )
			BEGIN
				SELECT @Avg_Standard_Cost = AVG( [Q1_Standard_Cost] ), @Avg_Standard_Yield = AVG( ISNULL( [Q1_Yield], 100.00 ) )
				FROM Update_View_Cost_Table WHERE ProcessStep_Id = @Process_ProcessStep_Id AND YEAR( Fin_Year ) = @YEAR 
				AND Q1_STANDARD_COST <> 0.00 AND Q1_STANDARD_COST IS NOT NULL
			END
			ELSE IF ( @QUARTER = 2 )
			BEGIN
				SELECT @Avg_Standard_Cost = AVG( [Q2_Standard_Cost] ), @Avg_Standard_Yield = AVG( ISNULL( [Q2_Yield], 100.00 ) ) 
				FROM Update_View_Cost_Table WHERE ProcessStep_Id = @Process_ProcessStep_Id AND YEAR( Fin_Year ) = @YEAR
				AND Q2_STANDARD_COST <> 0.00 AND Q2_STANDARD_COST IS NOT NULL
			END
			ELSE IF ( @QUARTER = 3 )
			BEGIN
				SELECT @Avg_Standard_Cost = AVG( [Q3_Standard_Cost] ), @Avg_Standard_Yield = AVG( ISNULL( [Q3_Yield], 100.00 ) )
				FROM Update_View_Cost_Table WHERE ProcessStep_Id = @Process_ProcessStep_Id AND YEAR( Fin_Year ) = @YEAR
				AND Q3_STANDARD_COST <> 0.00 AND Q3_STANDARD_COST IS NOT NULL
			END
			ELSE
			BEGIN
				SELECT @Avg_Standard_Cost = AVG( [Q4_Standard_Cost] ), @Avg_Standard_Yield = AVG( ISNULL( [Q4_Yield], 100.00 ) )
				FROM Update_View_Cost_Table WHERE ProcessStep_Id = @Process_ProcessStep_Id AND YEAR( Fin_Year ) = @YEAR
				AND Q4_STANDARD_COST <> 0.00 AND Q4_STANDARD_COST IS NOT NULL
			END

			-- IF THE YIELD IS 0.00 SET 100.00
			IF @Avg_Standard_Yield = 0.00
			BEGIN
				SET @Avg_Standard_Yield = 100.00
			END

			-- FETCH THE YIELDED COST
			SET @Avg_Actual_Yield = 100.00

			IF @Process_Type = 'FAB'
			BEGIN
				SELECT @Avg_Actual_Yield = AVG( ISNULL( FAB_LOT_HISTORY.FL_Yield, 100.00 ) ) FROM FAB_LOT_HISTORY, LOT_TABLE WHERE 
				FAB_LOT_HISTORY.Lot_ID = LOT_TABLE.LoT_Id AND ProcessStep_Id = @Process_ProcessStep_Id 
				AND FAB_LOT_HISTORY.FL_Date_Out >= @Quarter_Start_Date AND FAB_LOT_HISTORY.FL_Date_Out <= @Quarter_End_Date 
				AND FAB_LOT_HISTORY.FL_Current_Status = 'Close'
			END
			ELSE IF @Process_Type = 'SORT'
			BEGIN
				SELECT  @Avg_Actual_Yield = AVG( ISNULL( SORT_LOT_HISTORY.SL_Yield, 100.00 ) ) FROM SORT_LOT_HISTORY, LOT_TABLE WHERE 
				SORT_LOT_HISTORY.Lot_ID = LOT_TABLE.LoT_Id AND ProcessStep_Id = @Process_ProcessStep_Id 
				AND SORT_LOT_HISTORY.SL_Date_Out >= @Quarter_Start_Date	AND SORT_LOT_HISTORY.SL_Date_Out <= @Quarter_End_Date 
				AND SORT_LOT_HISTORY.SL_Current_Status = 'Close'
			END
			ELSE IF @Process_Type = 'Assembly'
			BEGIN
				SELECT  @Avg_Actual_Yield = AVG( ISNULL( Assembly_LOT_HISTORY.AL_Asy_Yield, 100.00 ) ) FROM Assembly_LOT_HISTORY, LOT_TABLE WHERE 
				Assembly_LOT_HISTORY.Lot_ID = LOT_TABLE.LoT_Id AND ProcessStep_Id = @Process_ProcessStep_Id 
				AND Assembly_LOT_HISTORY.AL_Date_Out >= @Quarter_Start_Date AND Assembly_LOT_HISTORY.AL_Date_Out <= @Quarter_End_Date 
				AND Assembly_LOT_HISTORY.AL_Current_Status = 'Close'
			END
			ELSE IF @Process_Type = 'TEST'
			BEGIN
				SELECT  @Avg_Actual_Yield = AVG( ISNULL( Final_Test_LOT_HISTORY.FTL_Yield, 100.00 ) ) FROM Final_Test_LOT_HISTORY, LOT_TABLE WHERE 
				Final_Test_LOT_HISTORY.Lot_ID = LOT_TABLE.LoT_Id AND ProcessStep_Id = @Process_ProcessStep_Id 
				AND Final_Test_LOT_HISTORY.FTL_Date_Out >= @Quarter_Start_Date AND Final_Test_LOT_HISTORY.FTL_Date_Out <= @Quarter_End_Date 
				AND Final_Test_LOT_HISTORY.FTL_Current_Status = 'Close'
			END
			/*ELSE IF @Process_Type = 'MARK & PACK'
			BEGIN
				-- NEED TO UPDATE AFTER THE TABLE COMPLETES
			END*/

			-- UPDATE THE VALUES TO THE RESULT TABLE
			/*
			In the Inventory Locations we are keeping the Same Yield as in Standard Yield, 
			because we will not be having any Yield at this point
			*/

			IF @Process_Type = 'FAB'
			BEGIN
				UPDATE @Result_Table SET FAB_Standard_Cost = @Avg_Standard_Cost + ISNULL ( FAB_Standard_Cost, 0.00 ), 
				FAB_Standard_yield = ( @Avg_Standard_Yield + ISNULL( FAB_Standard_yield , 100.00 ) )/2,
				FAB_Yielded_Yield = ( @Avg_Actual_Yield + ISNULL( FAB_Yielded_Yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'Wafer Store'
			BEGIN
				UPDATE @Result_Table SET WS_Standard_Cost = @Avg_Standard_Cost + ISNULL ( WS_Standard_Cost, 0.00 ), 
				WS_Standard_yield = ( @Avg_Standard_Yield + ISNULL( WS_Standard_yield , 100.00 ) )/2,
				WS_Yielded_yield = ( @Avg_Standard_Yield + ISNULL( WS_Standard_yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'SORT'
			BEGIN
				UPDATE @Result_Table SET SORT_Standard_Cost = @Avg_Standard_Cost + ISNULL ( SORT_Standard_Cost, 0.00 ), 
				SORT_Standard_yield = ( @Avg_Standard_Yield + ISNULL( SORT_Standard_yield , 100.00 ) )/2,
				SORT_Yielded_yield = ( @Avg_Actual_Yield + ISNULL( SORT_Yielded_yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'Die Bank'
			BEGIN
				UPDATE @Result_Table SET DB_Standard_Cost = @Avg_Standard_Cost + ISNULL ( DB_Standard_Cost, 0.00 ), 
				DB_Standard_yield = ( @Avg_Standard_Yield + ISNULL( DB_Standard_yield , 100.00 ) )/2,
				DB_Yielded_yield = ( @Avg_Standard_Yield + ISNULL( DB_Standard_yield , 100.00 ) )/2 
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'Assembly'
			BEGIN
				UPDATE @Result_Table SET ASSM_Standard_Cost = @Avg_Standard_Cost + ISNULL ( ASSM_Standard_Cost, 0.00 ), 
				ASSM_Standard_yield = ( @Avg_Standard_Yield + ISNULL( ASSM_Standard_yield , 100.00 ) )/2, 
				ASSM_Yielded_yield = ( @Avg_Actual_Yield + ISNULL( ASSM_Yielded_yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'TEST'
			BEGIN
				UPDATE @Result_Table SET TEST_Standard_Cost = @Avg_Standard_Cost + ISNULL ( TEST_Standard_Cost, 0.00 ), 
				TEST_Standard_yield = ( @Avg_Standard_Yield + ISNULL( TEST_Standard_yield , 100.00 ) )/2,
				TEST_Yielded_yield = ( @Avg_Actual_Yield + ISNULL( TEST_Yielded_yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'Class Store'
			BEGIN
				UPDATE @Result_Table SET CS_Standard_Cost = @Avg_Standard_Cost + ISNULL ( CS_Standard_Cost, 0.00 ), 
				CS_Standard_yield = ( @Avg_Standard_Yield + ISNULL( CS_Standard_yield , 100.00 ) )/2,
				CS_Yielded_yield = ( @Avg_Standard_Yield + ISNULL( CS_Standard_yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'MARK & PACK'
			BEGIN
				UPDATE @Result_Table SET MP_Standard_Cost = @Avg_Standard_Cost + ISNULL ( MP_Standard_Cost, 0.00 ), 
				MP_Standard_yield = ( @Avg_Standard_Yield + ISNULL( MP_Standard_yield , 100.00 ) )/2, 
				MP_Yielded_yield = ( @Avg_Actual_Yield + ISNULL( MP_Yielded_yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END
			ELSE IF @Process_Type = 'Finished Goods'
			BEGIN
				UPDATE @Result_Table SET FG_Standard_Cost = @Avg_Standard_Cost + ISNULL ( FG_Standard_Cost, 0.00 ), 
				FG_Standard_yield = ( @Avg_Standard_Yield + ISNULL( FG_Standard_yield , 100.00 ) )/2,
				FG_Yielded_yield = ( @Avg_Standard_Yield + ISNULL( FG_Standard_yield , 100.00 ) )/2
				WHERE ProcessStep_Id = @FG_ProcessStep_Id
			END

			PRINT @Avg_Actual_Yield
			PRINT @Process_ProcessStep_Id

			FETCH NEXT FROM @Process_Cursor INTO @Process_ProcessStep_Id, @Process_Type
		END
		CLOSE @Process_Cursor
		DEALLOCATE @Process_Cursor
		
		FETCH NEXT FROM @FG_CURSOR INTO @FG_ProcessStep_Id
	END
	CLOSE @FG_CURSOR
	DEALLOCATE @FG_CURSOR
	
	SELECT * FROM  @Result_Table

END